package com.sharp.framework.service.excel;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.sharp.framework.config.anno.EnumDict;
import com.sharp.framework.dao.entity.DictionaryItem;
import com.sharp.framework.frame.cache.base.IDynamicDict;
import com.sharp.framework.service.cache.FrameCacheService;
import com.sharp.kernel.constant.dict.MetaDict;
import com.sharp.kernel.utils.DateUtil;
import com.sharp.kernel.utils.JsonUtil;
import com.sharp.kernel.utils.MathUtil;
import com.sharp.kernel.utils.ReflectionUtil;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.*;
import java.lang.reflect.Field;
import java.math.RoundingMode;
import java.util.*;

import static org.apache.poi.ss.usermodel.DateUtil.getJavaDate;

/**
 * Title: ExcelService
 * Description: Copyright: Copyright (c) 2019 Company: BHFAE
 *
 * @author Sharp
 * @date 2022/6/8 17:41
 */
@Slf4j
@Service
public class ExcelService {
    public final String EXCEL_SUFFIX = ".xlsx";
    public final String DEFAULT_SHEET = "result";
    private final int MAX_ROW = 1048575;

    @Resource
    protected FrameCacheService frameCacheService;

    public XSSFWorkbook generateExcelMultiSheet(Map<String, Object> map) {
        XSSFWorkbook wb = new XSSFWorkbook();
        map.forEach((key, value) -> generateExcel(value, key, wb));
        return wb;
    }

    @SuppressWarnings("unchecked")
    public XSSFWorkbook generateExcel(Object object, String sheetName) {
        return generateExcel(object, sheetName, null);
    }

    @SuppressWarnings("unchecked")
    public XSSFWorkbook generateExcel(Object object, String sheetName, XSSFWorkbook wb) {
        Class<?> itemClass;
        if (object instanceof ArrayList<?>) {
            itemClass = ((ArrayList<Object>) object).get(0).getClass();
        } else if (object instanceof Class) {
            itemClass = (Class<?>) object;
        } else {
            log.error("The generate Object is not ArrayList");
            itemClass = object.getClass();
        }
        Map<Field, String> fieldTitleMap = buildFieldTitleMap(itemClass);
        List<Field> fields = ReflectionUtil.getFields(itemClass);
        if (wb == null) {
            wb = new XSSFWorkbook();
        }
        XSSFSheet sheet = createSheet(wb, sheetName, fieldTitleMap);
        XSSFCellStyle cellStyle = setContentCellStyle(wb);
        if (!(object instanceof ArrayList<?>) || ((ArrayList<Object>) object).isEmpty()) {
            log.error("The generate list is empty");
            formatSheet(wb, fields);
            return wb;
        }
        ArrayList<Object> list = (ArrayList<Object>) object;
        for (int line = 0; line < list.size(); line++) {
            XSSFRow row = sheet.createRow(line + 1);
            int columnIndex = 0;
            for (Map.Entry<Field, String> entry : fieldTitleMap.entrySet()) {
                JsonIgnore jsonIgnoreAnnotation = entry.getKey().getAnnotation(JsonIgnore.class);
                if (jsonIgnoreAnnotation != null) {
                    continue;
                }
                XSSFCell cell = row.createCell(columnIndex++);
                cell.setCellStyle(cellStyle);
                String value = null;
                Object obj = ReflectionUtil.getValue(list.get(line), entry.getKey());
                if (obj != null) {
                    if (obj instanceof Date) {
                        DateTimeFormat dateTimeFormatAnnotation = entry.getKey().getAnnotation(DateTimeFormat.class);
                        if (dateTimeFormatAnnotation != null &&
                                StringUtils.isNotEmpty((dateTimeFormatAnnotation).pattern())) {
                            value = DateUtil.formatDate((Date) obj, dateTimeFormatAnnotation.pattern());
                        } else {
                            value = DateUtil.formatDate((Date) obj, DateUtil.DATE_TIME_NORMAL);
                        }
                    } else {
                        value = obj.toString();
                    }
                }
                cell.setCellValue(value);
            }
        }
        formatSheet(wb, fields);
        return wb;
    }

    private Map<Field, String> buildFieldTitleMap(Class<?> clazz) {
        Map<Field, String> fieldTitleMap = new LinkedHashMap<>(0);
        Map<String, Field> fieldMap = ReflectionUtil.getFieldMap(clazz);

        for (Map.Entry<String, Field> field : fieldMap.entrySet()) {
            JsonIgnore jsonIgnoreAnnotation = field.getValue().getAnnotation(JsonIgnore.class);
            if (jsonIgnoreAnnotation != null) {
                continue;
            }
            ApiModelProperty apiModelAnnotation = field.getValue().getAnnotation(ApiModelProperty.class);
            if (apiModelAnnotation != null && StringUtils.isNotEmpty((apiModelAnnotation).value())) {
                fieldTitleMap.put(field.getValue(), apiModelAnnotation.value());
            }
        }
        return fieldTitleMap;
    }

    private XSSFSheet createSheet(XSSFWorkbook wb, String sheetName, Map<Field, String> fieldTitleMap) {
        XSSFSheet sheet = wb.createSheet(sheetName);
        sheet.createFreezePane(0, 1, 0, 1);
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle colorStyle = setTitleCellStyle(wb);
        XSSFCellStyle textStyle = buildXssfCellTextStyle(wb);
        int index = 0;
        for (Map.Entry<Field, String> entry : fieldTitleMap.entrySet()) {
            XSSFCell titleCell = titleRow.createCell(index);
            titleCell.setCellStyle(colorStyle);
            titleCell.setCellValue(entry.getValue());
            EnumDict dictEnumAnnotation = entry.getKey().getAnnotation(EnumDict.class);
            if (dictEnumAnnotation != null) {
                List<String> dataList = new ArrayList<>();
                Class<?> dictClazz = dictEnumAnnotation.dict();
                if (Enum.class.isAssignableFrom(dictClazz)) {
                    if (dictEnumAnnotation.dict().getEnumConstants() != null) {
                        for (Object item : dictEnumAnnotation.dict().getEnumConstants()) {
                            dataList.add(
                                    ReflectionUtil.getValue(item, dictEnumAnnotation.displayFieldName(), String.class));
                        }
                    }
                } else if (IDynamicDict.class.isAssignableFrom(dictEnumAnnotation.dict())) {
                    String dictId = dictClazz.getAnnotation(MetaDict.class).value();
                    List<DictionaryItem> items = frameCacheService.getDictItemList(dictId);
                    if (CollectionUtils.isNotEmpty(items)) {
                        for (DictionaryItem item : items) {
                            dataList.add(item.getRemark());
                        }
                    }
                }
                setValidateData(sheet, dataList.toArray(new String[0]), index);
            }
            sheet.setDefaultColumnStyle(index, textStyle);
            index++;
        }
        return sheet;
    }

    protected XSSFCellStyle setContentCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setLocked(false);
        cellStyle.setShrinkToFit(true);
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
    }

    protected void formatSheet(XSSFWorkbook wb, Collection<?> fields) {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            XSSFSheet sheet = wb.getSheetAt(i);
            for (int j = 0; j < fields.size(); j++) {
                sheet.autoSizeColumn(j);
            }
            setSizeColumn(sheet, fields);
        }
    }

    protected XSSFCellStyle setTitleCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setLocked(true);
        cellStyle.setShrinkToFit(true);
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }

    protected XSSFCellStyle buildXssfCellTextStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        XSSFDataFormat dataFormat = wb.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        return cellStyle;
    }

    protected void setValidateData(XSSFSheet sheet, String[] dataArray, int col) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint =
                (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(
                        dataArray);
        CellRangeAddressList addressList = new CellRangeAddressList(1, MAX_ROW, col, col);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        //这两行设置单元格只能是列表中的内容，否则报错
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        validation.setShowPromptBox(true);
        validation.setEmptyCellAllowed(false);

        sheet.addValidationData(validation);
    }

    private void setSizeColumn(XSSFSheet sheet, Collection<?> fields) {
        for (int columnNum = 0; columnNum <= fields.size(); columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    XSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256 + 184);
            // Refer to
            // https://blog.csdn.net/duqian42707/article/details/51491312
        }
    }

    public int generateExcelByLabelValue(List<Map<String, String>> object, OutputStream outputStream) {
        Map<String, List<Map<String, String>>> data = new HashMap<>(1);
        data.put("sheet1", object);
        return generateExcelByLabelValue(data, outputStream);
    }

    public int generateExcelByLabelValue(Map<String, List<Map<String, String>>> object, OutputStream outputStream) {
        XSSFWorkbook xssfWorkbook = generateExcelByLabelValue(object);
        try {
            outputStream.flush();
            xssfWorkbook.write(outputStream);
            outputStream.close();
            return 1;
        } catch (IOException e) {
            log.error("生成EXCEL失败", e);
        }
        return -1;
    }

    public XSSFWorkbook generateExcelByLabelValue(Map<String, List<Map<String, String>>> map) {
        XSSFWorkbook wb = new XSSFWorkbook();
        map.forEach((key, value) -> generateExcel(value, key, wb));
        return wb;
    }

    public XSSFWorkbook generateExcel(List<Map<String, String>> values, String sheetName, XSSFWorkbook wb) {
        if (wb == null) {
            wb = new XSSFWorkbook();
        }
        if (CollectionUtils.isEmpty(values)) {
            log.error("生成Excel数据不存在");
            return wb;
        }
        XSSFSheet sheet = createSheetWithName(wb, sheetName, values.get(0).keySet());
        XSSFCellStyle cellStyle = setContentCellStyle(wb);
        for (int line = 0; line < values.size(); line++) {
            XSSFRow row = sheet.createRow(line + 1);
            int columnIndex = 0;
            for (Map.Entry<String, String> entry : values.get(line).entrySet()) {
                XSSFCell cell = row.createCell(columnIndex++);
                cell.setCellStyle(cellStyle);
                String value = entry.getValue();
                cell.setCellValue(value);
            }
        }
        formatSheet(wb, values.get(0).values());
        return wb;
    }

    private XSSFSheet createSheetWithName(XSSFWorkbook wb, String sheetName, Collection<String> titles) {
        XSSFSheet sheet = wb.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        sheet.createFreezePane(0, 1, 0, 1);
        XSSFCellStyle colorStyle = setTitleCellStyle(wb);
        XSSFCellStyle textStyle = buildXssfCellTextStyle(wb);
        int index = 0;
        for (String title : titles) {
            sheet.setDefaultColumnStyle(index, textStyle);
            XSSFCell titleCell = titleRow.createCell(index++);
            titleCell.setCellStyle(colorStyle);
            titleCell.setCellValue(title);
        }
        return sheet;
    }

    public int generateExcel(Object object, OutputStream outputStream) {
        XSSFWorkbook xssfWorkbook;
        if (object instanceof Map) {
            xssfWorkbook = generateExcelMultiSheet((Map) object);
        } else {
            xssfWorkbook = generateExcel(object);
        }
        try {
            outputStream.flush();
            xssfWorkbook.write(outputStream);
            outputStream.close();
            return 1;
        } catch (IOException e) {
            log.error("生成EXCEL失败", e);
        }
        return -1;
    }

    public int generateExcel(Object object, File file) {
        if (object == null || !file.getName().endsWith(EXCEL_SUFFIX)) {
            return -1;
        }
        XSSFWorkbook xssfWorkbook = generateExcel(object);
        try (FileOutputStream outputStream = new FileOutputStream(file)) {
            outputStream.flush();
            if (xssfWorkbook != null) {
                xssfWorkbook.write(outputStream);
                outputStream.close();
                return 1;
            }
        } catch (IOException e) {
            log.error("生成EXCEL失败", e);
        }
        return -1;
    }

    public XSSFWorkbook generateExcel(Object object) {
        return generateExcel(object, DEFAULT_SHEET, null);
    }

    public InputStream convertInputStream(XSSFWorkbook workbook) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }

        byte[] content = os.toByteArray();
        return new ByteArrayInputStream(content);
    }

    public <T> ArrayList<T> readFromExcel(MultipartFile file, Class<T> clazz) {
        File f;
        ArrayList<T> objList = null;
        try {
            f = File.createTempFile("tmp", null);
            file.transferTo(f);
            objList = readFromExcel(f.getAbsolutePath(), clazz);
            f.deleteOnExit();
        } catch (IllegalStateException | IOException e) {
            log.error("读取EXCEL失败", e);
        }
        return objList;
    }

    public <T> ArrayList<T> readFromExcel(String filePath, Class<T> clazz) {
        FileInputStream fs;
        ArrayList<T> objList = null;
        try {
            fs = new FileInputStream(filePath);
            try {
                // 使用POI提供的方法得到excel的信息
                POIFSFileSystem ps = new POIFSFileSystem(fs);
                HSSFWorkbook wb = new HSSFWorkbook(ps);
                objList = readFromExcel(wb, clazz);
            } catch (Exception e) {
                fs = new FileInputStream(filePath);
                XSSFWorkbook wb = new XSSFWorkbook(fs);
                objList = readFromExcel(wb, clazz);
            } finally {
                fs.close();
            }
        } catch (Exception e) {
            log.error("生成EXCEL失败", e);
        }
        return objList;
    }

    private <T> ArrayList<T> readFromExcel(HSSFWorkbook wb, Class<T> clazz) {
        int startRow = 1;
        return readFromExcel(startRow, wb, clazz);
    }

    private <T> ArrayList<T> readFromExcel(XSSFWorkbook wb, Class<T> clazz) {
        int startRow = 1;
        return readFromExcel(startRow, wb, clazz);
    }

    private <T> ArrayList<T> readFromExcel(Integer startRow, HSSFWorkbook wb, Class<T> clazz) {
        ArrayList<T> resList = new ArrayList<>();
        // 获取到工作表，因为一个excel可能有多个工作表
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            HSSFSheet sheet = wb.getSheetAt(sheetIndex);
            int maxline = sheet.getLastRowNum();
            Map<Field, String> fieldTitleMap = buildFieldTitleMap(clazz);
            Map<Field, Integer> fieldColumnMap = buildFieldColumnMap(startRow - 1, sheet, fieldTitleMap);
            if (fieldColumnMap.entrySet().size() > 0) {
                for (int line = startRow; line <= maxline; line++) {
                    HSSFRow row = sheet.getRow(line);
                    if (row == null) {
                        continue;
                    }
                    try {
                        T res = clazz.newInstance();
                        boolean update = false;
                        for (Map.Entry<Field, Integer> entry : fieldColumnMap.entrySet()) {
                            HSSFCell cell = row.getCell(entry.getValue());
                            Object obj = null;
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case STRING:
                                        obj = cell.getStringCellValue();
                                        break;
                                    case NUMERIC:
                                        if (entry.getKey().getType().equals(String.class)) {
                                            obj = MathUtil.convert(cell.getNumericCellValue())
                                                    .setScale(0, RoundingMode.HALF_UP)
                                                    .toPlainString();
                                        } else if (entry.getKey().getType().equals(Date.class)) {
                                            obj = getJavaDate(cell.getNumericCellValue());
                                        } else {
                                            obj = cell.getNumericCellValue();
                                        }
                                        break;
                                    case BOOLEAN:
                                        obj = cell.getBooleanCellValue();
                                        break;
                                    default:
                                        obj = null;
                                }
                            }
                            if (obj != null) {
                                try {
                                    Object value;
                                    DateTimeFormat dateTimeFormatAnnotation = entry.getKey()
                                            .getAnnotation(DateTimeFormat.class);
                                    if (dateTimeFormatAnnotation != null &&
                                            StringUtils.isNotEmpty((dateTimeFormatAnnotation).pattern())) {
                                        value = JsonUtil.readDateJson(obj, dateTimeFormatAnnotation.pattern(),
                                                entry.getKey().getType());
                                    } else {
                                        value = JsonUtil.readJson(obj, entry.getKey().getType());
                                    }
                                    ReflectionUtil.setFieldValue(res, entry.getKey().getName(), value);
                                    update = true;
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                        if (update) {
                            resList.add(res);
                        }
                    } catch (InstantiationException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return resList;
    }

    private <T> ArrayList<T> readFromExcel(Integer startRow, XSSFWorkbook wb, Class<T> clazz) {
        ArrayList<T> resList = new ArrayList<>();
        // 获取到工作表，因为一个excel可能有多个工作表
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            XSSFSheet sheet = wb.getSheetAt(sheetIndex);
            int maxline = sheet.getLastRowNum();
            Map<Field, String> fieldTitleMap = buildFieldTitleMap(clazz);
            Map<Field, Integer> fieldColumnMap = buildFieldColumnMap(startRow - 1, sheet, fieldTitleMap);
            if (fieldColumnMap.entrySet().size() > 0) {
                for (int line = startRow; line <= maxline; line++) {
                    XSSFRow row = sheet.getRow(line);
                    if (row == null) {
                        continue;
                    }
                    try {
                        T res = clazz.newInstance();
                        boolean update = false;
                        for (Map.Entry<Field, Integer> entry : fieldColumnMap.entrySet()) {
                            XSSFCell cell = row.getCell(entry.getValue());
                            Object obj = null;
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case STRING:
                                        obj = cell.getStringCellValue();
                                        break;
                                    case NUMERIC:
                                        if (entry.getKey().getType().equals(String.class)) {
                                            obj = MathUtil.convert(cell.getNumericCellValue())
                                                    .setScale(0, RoundingMode.HALF_UP)
                                                    .toPlainString();
                                        } else if (entry.getKey().getType().equals(Date.class)) {
                                            obj = getJavaDate(cell.getNumericCellValue());
                                        } else {
                                            obj = cell.getNumericCellValue();
                                        }
                                        break;
                                    case BOOLEAN:
                                        obj = cell.getBooleanCellValue();
                                        break;
                                    default:
                                        obj = null;
                                }
                            }
                            if (obj != null) {
                                try {
                                    Object value;
                                    DateTimeFormat dateTimeFormatAnnotation = entry.getKey()
                                            .getAnnotation(DateTimeFormat.class);
                                    if (dateTimeFormatAnnotation != null &&
                                            StringUtils.isNotEmpty((dateTimeFormatAnnotation).pattern())) {
                                        value = JsonUtil.readDateJson(obj, dateTimeFormatAnnotation.pattern(),
                                                entry.getKey().getType());
                                    } else {
                                        value = JsonUtil.readJson(obj, entry.getKey().getType());
                                    }
                                    ReflectionUtil.setFieldValue(res, entry.getKey().getName(), value);
                                    update = true;
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                        if (update) {
                            resList.add(res);
                        }
                    } catch (InstantiationException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return resList;
    }

    private Map<Field, Integer> buildFieldColumnMap(Integer startRow, HSSFSheet sheet,
                                                    Map<Field, String> fieldTitleMap) {
        Map<Field, Integer> map = new LinkedHashMap<>(fieldTitleMap.entrySet().size());
        HSSFRow titleRow = sheet.getRow(startRow);
        return buildFieldColumnMap(titleRow, fieldTitleMap, map);
    }

    private Map<Field, Integer> buildFieldColumnMap(Integer startRow, XSSFSheet sheet,
                                                    Map<Field, String> fieldTitleMap) {
        Map<Field, Integer> map = new LinkedHashMap<>(fieldTitleMap.entrySet().size());
        XSSFRow titleRow = sheet.getRow(startRow);
        return buildFieldColumnMap(titleRow, fieldTitleMap, map);
    }

    private Map<Field, Integer> buildFieldColumnMap(HSSFRow titleRow, Map<Field, String> fieldTitleMap,
                                                    Map<Field, Integer> map) {

        int maxColumn = titleRow.getLastCellNum();
        for (int column = 0; column < maxColumn; column++) {
            HSSFCell cell = titleRow.getCell(column);
            if (cell != null) {
                for (Map.Entry<Field, String> entry : fieldTitleMap.entrySet()) {
                    if (entry.getValue().equals(cell.getStringCellValue())) {
                        map.put(entry.getKey(), column);
                    }
                }
            }
        }
        return map;
    }

    private Map<Field, Integer> buildFieldColumnMap(XSSFRow titleRow, Map<Field, String> fieldTitleMap,
                                                    Map<Field, Integer> map) {

        int maxColumn = titleRow.getLastCellNum();
        for (int column = 0; column < maxColumn; column++) {
            XSSFCell cell = titleRow.getCell(column);
            if (cell != null) {
                for (Map.Entry<Field, String> entry : fieldTitleMap.entrySet()) {
                    if (entry.getValue().equals(cell.getStringCellValue())) {
                        map.put(entry.getKey(), column);
                    }
                }
            }
        }
        return map;
    }

    public <T> ArrayList<T> readFromExcel(File file, Class<T> clazz) {
        ArrayList<T> objList = readFromExcel(file.getAbsolutePath(), clazz);
        file.deleteOnExit();
        return objList;
    }

    public <T> ArrayList<T> readFromExcelByStartRow(Integer startRow, File file, Class<T> clazz) {
        ArrayList<T> objList = readFromExcel(startRow, file.getAbsolutePath(), clazz);
        file.deleteOnExit();
        return objList;
    }

    public <T> ArrayList<T> readFromExcel(Integer index, String filePath, Class<T> clazz) {
        FileInputStream fs;
        ArrayList<T> objList = null;
        try {
            fs = new FileInputStream(filePath);
            try {
                // 使用POI提供的方法得到excel的信息
                POIFSFileSystem ps = new POIFSFileSystem(fs);
                HSSFWorkbook wb = new HSSFWorkbook(ps);
                objList = readFromExcel(index, wb, clazz);
            } catch (Exception e) {
                fs = new FileInputStream(filePath);
                XSSFWorkbook wb = new XSSFWorkbook(fs);
                objList = readFromExcel(index, wb, clazz);
            } finally {
                fs.close();
            }
        } catch (Exception e) {
            log.error("生成EXCEL失败", e);
        }
        return objList;
    }
}
